﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace jsm.SqlHelper
{
    /// <summary>
    /// SqlServer database operations class.
    /// </summary>
    public partial class SqlServerHelper:HelperBase
    {
        public new SqlConnection Connection
        {
            get { return (SqlConnection)base.Connection; }
            set { base.Connection = value; }
        }
        public new SqlCommand Command
        {
            get { return (SqlCommand)base.Command; }
            set { base.Command = value; }
        }

        public SqlServerHelper()
        {
            base.ConnectionString = SqlServerHelper.default_connection_str;
            Connection = new SqlConnection();
            base.Command = Connection.CreateCommand();
        }

        public SqlServerHelper(int ConnectionStringsIndex)
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringsIndex].ConnectionString;
            Connection = new SqlConnection();
            base.Command = Connection.CreateCommand();
        }

        public SqlServerHelper(string ConnectionString)
        {
            this.ConnectionString = ConnectionString;
            Connection = new SqlConnection();
            base.Command = Connection.CreateCommand();
        }

        public SqlParameter AddParameter(string ParameterName, SqlDbType type, object value)
        {
            return AddParameter(ParameterName, type, value, ParameterDirection.Input);
        }

        public SqlParameter AddParameter(string ParameterName, SqlDbType type, object value, ParameterDirection direction)
        {
            SqlParameter param = new SqlParameter(ParameterName, type);
            param.Value = value;
            param.Direction = direction;
            Command.Parameters.Add(param);
            return param;
        }

        public SqlParameter AddParameter(string ParameterName, SqlDbType type, int size, object value)
        {
            return AddParameter(ParameterName, type, size, value, ParameterDirection.Input);
        }

        public SqlParameter AddParameter(string ParameterName, SqlDbType type, int size, object value, ParameterDirection direction)
        {
            SqlParameter param = new SqlParameter(ParameterName, type, size);
            param.Direction = direction;
            param.Value = value;
            Command.Parameters.Add(param);
            return param;
        }

        public void AddRangeParameters(SqlParameter[] parameters)
        {
            Command.Parameters.AddRange(parameters);
        }

        public bool InsertTable(TableFramework FrameWork)
        {
            return InsertTable(FrameWork, true);
        }
        public bool InsertTable(TableFramework FrameWork, bool newCommand)
        {
            SqlCommand cmd;
            if (newCommand)
            {
                cmd =(SqlCommand) Connection.CreateCommand();
                cmd.Transaction = this.Command.Transaction;
            }
            else cmd = Command;


            if (FrameWork.TableName == null)
                FrameWork.TableName = TableName;
            string sql = "insert into [" + FrameWork.TableName + "](";
            string Names = "";
            string Values = "";
            foreach (TableFramework.Column col in FrameWork)
            {
                if (Names != "")
                {
                    Names += ",";
                    Values += ",";
                }
                Names += "[" + col.Name + "]";
                Values += "@" + col.Name;
                if (col.CType == typeof(string))
                {
                    cmd.Parameters.Add(new SqlParameter("@" + col.Name, col.Value));
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@" + col.Name, col.Value));
                }
            }
            if (Names == "")
                throw new Exception("Table's FrameWork is not Empty！");
            sql += Names + ")values(" + Values + ")";
            cmd.CommandText = sql;
            return cmd.ExecuteNonQuery() > 0;
        }
        public bool UpdateTable(TableFramework FrameWork, string whereText, params SqlParameter[] ps)
        {
            return UpdateTable(FrameWork, whereText, true,ps);
        }
        public bool UpdateTable(TableFramework FrameWork, string whereText, bool newcommand, params SqlParameter[] ps)
        {
            if (FrameWork.TableName == null)
                FrameWork.TableName = TableName;

            SqlCommand cmd;
            if (newcommand) cmd = (SqlCommand)Connection.CreateCommand();
            else cmd = Command;
            string sql = "update [" + FrameWork.TableName + "] set";
            string setText = "";
            foreach (TableFramework.Column col in FrameWork)
            {
                if (setText != "")
                {
                    setText += ",";
                }
                setText += "[" + col.Name + "]=@" + col.Name;
                cmd.Parameters.Add(new SqlParameter("@" + col.Name, col.Value));
            }
            if (setText == "")
                throw new Exception("Table's FrameWork is not Empty！");
            sql += setText + " " + whereText;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(ps);
            int i = cmd.ExecuteNonQuery();
            return i > 0;
        }
    }
}
